"""
剧场列表API服务 - theater_list.py
提供剧场数据查询的HTTP接口服务
支持列表查询、详情获取、统计分析、数据导出等功能
"""

import json
import os
from datetime import datetime
from typing import Dict, List, Any, Optional
from dataclasses import dataclass
from contextlib import asynccontextmanager

from fastapi import FastAPI, HTTPException, Query, Path
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse, FileResponse
from pydantic import BaseModel, Field
import uvicorn

from database import get_mysql_manager

# ==================== 数据模型 ====================

class ApiResponse(BaseModel):
    success: bool
    message: str
    data: Optional[Dict[str, Any]] = None
    error_code: Optional[str] = None

class TheaterListResponse(BaseModel):
    success: bool
    total: int
    count: int
    limit: Optional[int]
    offset: int
    search: Optional[str]
    theaters: List[Dict[str, Any]]
    query_time: str

class TheaterDetailResponse(BaseModel):
    success: bool
    theater: Optional[Dict[str, Any]]
    query_time: str

class StatisticsResponse(BaseModel):
    success: bool
    statistics: Dict[str, Any]
    query_time: str

class ExportRequest(BaseModel):
    format: str = Field(default="json", description="导出格式: json, csv, summary")
    limit: Optional[int] = Field(default=None, description="限制数量")
    offset: int = Field(default=0, description="偏移量")
    search: Optional[str] = Field(default=None, description="搜索关键词")
    filename: Optional[str] = Field(default=None, description="文件名")

# ==================== 核心业务类 ====================

class TheaterListManager:
    def __init__(self):
        """初始化剧场列表管理器"""
        try:
            self.mysql_manager = get_mysql_manager()
            print("✅ 数据库连接初始化成功")
        except Exception as e:
            print(f"❌ 数据库连接失败: {e}")
            self.mysql_manager = None

    def get_all_theaters(self, limit: int = None, offset: int = 0, search: str = None) -> Dict[str, Any]:
        """获取所有剧场列表"""
        try:
            if not self.mysql_manager:
                return {"success": False, "error": "数据库未初始化"}

            with self.mysql_manager.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 构建查询条件
                where_clause = ""
                params = []
                
                if search:
                    where_clause = "WHERE title LIKE %s OR description LIKE %s OR era LIKE %s OR location LIKE %s"
                    search_pattern = f"%{search}%"
                    params = [search_pattern, search_pattern, search_pattern, search_pattern]
                
                # 获取总数
                count_sql = f"SELECT COUNT(*) as total FROM theaters {where_clause}"
                cursor.execute(count_sql, params)
                total = cursor.fetchone()['total']
                
                # 构建查询SQL
                base_sql = f"""
                    SELECT 
                        id,
                        title,
                        description,
                        era,
                        location,
                        plot_summary,
                        features,
                        author,
                        task_id,
                        created_at,
                        updated_at,
                        (SELECT COUNT(*) FROM characters WHERE theater_id = theaters.id) as character_count,
                        (SELECT COUNT(*) FROM script_nodes WHERE theater_id = theaters.id) as node_count,
                        (SELECT COUNT(*) FROM skills_pool WHERE theater_id = theaters.id) as skill_count,
                        (SELECT COUNT(*) FROM chat_sessions WHERE theater_id = theaters.id) as session_count,
                        (SELECT COUNT(*) FROM chat_messages WHERE theater_id = theaters.id) as message_count
                    FROM theaters 
                    {where_clause}
                    ORDER BY created_at DESC
                """
                
                # 添加分页
                if limit:
                    base_sql += f" LIMIT {limit} OFFSET {offset}"
                
                cursor.execute(base_sql, params)
                theaters = cursor.fetchall()
                
                # 处理 JSON 字段
                for theater in theaters:
                    if theater['features']:
                        try:
                            theater['features'] = json.loads(theater['features'])
                        except:
                            theater['features'] = []
                    else:
                        theater['features'] = []
                    
                    # 转换时间格式
                    if theater['created_at']:
                        theater['created_at'] = theater['created_at'].isoformat()
                    if theater['updated_at']:
                        theater['updated_at'] = theater['updated_at'].isoformat()
                
                cursor.close()
                
                return {
                    "success": True,
                    "total": total,
                    "count": len(theaters),
                    "limit": limit,
                    "offset": offset,
                    "search": search,
                    "theaters": theaters,
                    "query_time": datetime.now().isoformat()
                }
                
        except Exception as e:
            return {
                "success": False,
                "error": str(e),
                "query_time": datetime.now().isoformat()
            }

    def get_theater_by_id(self, theater_id: int) -> Dict[str, Any]:
        """根据ID获取单个剧场详细信息"""
        try:
            if not self.mysql_manager:
                return {"success": False, "error": "数据库未初始化"}

            theater_data = self.mysql_manager.get_theater_by_id(theater_id)
            
            if theater_data:
                # 添加统计信息
                with self.mysql_manager.get_connection() as connection:
                    cursor = connection.cursor(dictionary=True)
                    
                    # 获取聊天统计
                    cursor.execute("""
                        SELECT 
                            COUNT(DISTINCT cs.session_id) as total_sessions,
                            COUNT(cm.id) as total_messages,
                            AVG(cs.total_messages) as avg_messages_per_session
                        FROM chat_sessions cs
                        LEFT JOIN chat_messages cm ON cs.session_id = cm.session_id
                        WHERE cs.theater_id = %s
                    """, (theater_id,))
                    
                    chat_stats = cursor.fetchone()
                    cursor.close()
                
                theater_data['chat_statistics'] = chat_stats or {
                    'total_sessions': 0,
                    'total_messages': 0,
                    'avg_messages_per_session': 0
                }
                
                return {
                    "success": True,
                    "theater": theater_data,
                    "query_time": datetime.now().isoformat()
                }
            else:
                return {
                    "success": False,
                    "error": f"剧场 ID {theater_id} 不存在",
                    "query_time": datetime.now().isoformat()
                }
                
        except Exception as e:
            return {
                "success": False,
                "error": str(e),
                "query_time": datetime.now().isoformat()
            }

    def get_theater_statistics(self) -> Dict[str, Any]:
        """获取剧场统计信息"""
        try:
            if not self.mysql_manager:
                return {"success": False, "error": "数据库未初始化"}

            with self.mysql_manager.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 基础统计
                cursor.execute("""
                    SELECT 
                        COUNT(*) as total_theaters,
                        COUNT(DISTINCT author) as unique_authors,
                        AVG((SELECT COUNT(*) FROM characters WHERE theater_id = theaters.id)) as avg_characters,
                        AVG((SELECT COUNT(*) FROM script_nodes WHERE theater_id = theaters.id)) as avg_nodes,
                        AVG((SELECT COUNT(*) FROM skills_pool WHERE theater_id = theaters.id)) as avg_skills
                    FROM theaters
                """)
                basic_stats = cursor.fetchone()
                
                # 按作者统计
                cursor.execute("""
                    SELECT author, COUNT(*) as count 
                    FROM theaters 
                    GROUP BY author 
                    ORDER BY count DESC
                    LIMIT 10
                """)
                by_author = cursor.fetchall()
                
                # 按时代统计
                cursor.execute("""
                    SELECT era, COUNT(*) as count 
                    FROM theaters 
                    WHERE era IS NOT NULL AND era != ''
                    GROUP BY era 
                    ORDER BY count DESC
                    LIMIT 10
                """)
                by_era = cursor.fetchall()
                
                # 按创建时间统计（最近30天）
                cursor.execute("""
                    SELECT 
                        DATE(created_at) as date,
                        COUNT(*) as count
                    FROM theaters 
                    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    GROUP BY DATE(created_at)
                    ORDER BY date DESC
                """)
                recent_activity = cursor.fetchall()
                
                # 聊天活动统计
                cursor.execute("""
                    SELECT 
                        COUNT(DISTINCT cs.theater_id) as theaters_with_chats,
                        COUNT(DISTINCT cs.session_id) as total_chat_sessions,
                        COUNT(cm.id) as total_chat_messages
                    FROM chat_sessions cs
                    LEFT JOIN chat_messages cm ON cs.session_id = cm.session_id
                """)
                chat_stats = cursor.fetchone()
                
                # 最活跃的剧场
                cursor.execute("""
                    SELECT 
                        t.id,
                        t.title,
                        COUNT(DISTINCT cs.session_id) as session_count,
                        COUNT(cm.id) as message_count
                    FROM theaters t
                    LEFT JOIN chat_sessions cs ON t.id = cs.theater_id
                    LEFT JOIN chat_messages cm ON cs.session_id = cm.session_id
                    GROUP BY t.id, t.title
                    ORDER BY message_count DESC
                    LIMIT 5
                """)
                most_active = cursor.fetchall()
                
                cursor.close()
                
                return {
                    "success": True,
                    "statistics": {
                        "basic": basic_stats,
                        "by_author": by_author,
                        "by_era": by_era,
                        "recent_activity": recent_activity,
                        "chat_activity": chat_stats,
                        "most_active_theaters": most_active
                    },
                    "query_time": datetime.now().isoformat()
                }
                
        except Exception as e:
            return {
                "success": False,
                "error": str(e),
                "query_time": datetime.now().isoformat()
            }

    def export_theaters_to_file(self, filename: str, format_type: str = "json", **kwargs) -> Dict[str, Any]:
        """导出剧场列表到文件"""
        try:
            # 确保导出目录存在
            export_dir = "theater_exports"
            os.makedirs(export_dir, exist_ok=True)
            
            # 完整文件路径
            file_path = os.path.join(export_dir, filename)
            
            # 获取数据
            data = self.get_all_theaters(**kwargs)
            
            if not data['success']:
                return data
            
            if format_type.lower() == "json":
                with open(file_path, 'w', encoding='utf-8') as f:
                    json.dump(data, f, ensure_ascii=False, indent=2)
            
            elif format_type.lower() == "csv":
                import csv
                with open(file_path, 'w', newline='', encoding='utf-8') as f:
                    if data['theaters']:
                        writer = csv.DictWriter(f, fieldnames=data['theaters'][0].keys())
                        writer.writeheader()
                        for theater in data['theaters']:
                            # 处理复杂字段
                            row = theater.copy()
                            if isinstance(row.get('features'), list):
                                row['features'] = ', '.join(row['features'])
                            writer.writerow(row)
            
            elif format_type.lower() == "summary":
                # 简化的 JSON 格式，只包含主要信息
                summary_data = {
                    "export_info": {
                        "total_theaters": data['total'],
                        "export_time": datetime.now().isoformat(),
                        "format": "summary"
                    },
                    "theaters": [
                        {
                            "id": t['id'],
                            "title": t['title'],
                            "description": t['description'][:100] + "..." if len(t.get('description', '')) > 100 else t.get('description', ''),
                            "era": t['era'],
                            "location": t['location'],
                            "author": t['author'],
                            "created_at": t['created_at'],
                            "stats": {
                                "characters": t['character_count'],
                                "nodes": t['node_count'],
                                "skills": t['skill_count'],
                                "sessions": t['session_count'],
                                "messages": t['message_count']
                            }
                        }
                        for t in data['theaters']
                    ]
                }
                
                with open(file_path, 'w', encoding='utf-8') as f:
                    json.dump(summary_data, f, ensure_ascii=False, indent=2)
            
            file_size = os.path.getsize(file_path)
            
            return {
                "success": True,
                "message": f"数据已导出到 {filename}",
                "filename": filename,
                "file_path": file_path,
                "format": format_type,
                "theaters_count": len(data['theaters']),
                "file_size_bytes": file_size
            }
            
        except Exception as e:
            return {
                "success": False,
                "error": str(e)
            }

# ==================== 全局变量 ====================

theater_manager = None

# ==================== 生命周期管理 ====================

@asynccontextmanager
async def lifespan(app: FastAPI):
    """FastAPI应用生命周期管理"""
    # 启动时执行
    print("🚀 启动剧场列表API服务...")
    
    global theater_manager
    try:
        theater_manager = TheaterListManager()
        if theater_manager.mysql_manager:
            print("✅ 剧场列表管理器初始化成功")
        else:
            print("❌ 数据库连接失败")
    except Exception as e:
        print(f"❌ 剧场列表管理器初始化失败: {e}")
        theater_manager = None
    
    yield  # 应用运行期间
    
    # 关闭时执行
    print("🛑 正在关闭剧场列表API服务...")
    
    # 关闭数据库连接
    if theater_manager and theater_manager.mysql_manager:
        theater_manager.mysql_manager.close()
        print("✅ 数据库连接已关闭")
    
    print("✅ 剧场列表API服务已关闭")

# ==================== FastAPI应用 ====================

app = FastAPI(
    title="剧场列表API",
    description="剧场数据查询和管理接口服务",
    version="1.0.0",
    docs_url="/docs",
    redoc_url="/redoc",
    lifespan=lifespan
)

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# ==================== API端点 ====================

@app.get("/", response_model=ApiResponse)
async def root():
    """根路径 - 服务信息"""
    return ApiResponse(
        success=True,
        message="剧场列表API服务正在运行",
        data={
            "version": "1.0.0",
            "docs": "/docs",
            "endpoints": {
                "theaters": "/theaters",
                "theater_detail": "/theaters/{id}",
                "statistics": "/statistics",
                "export": "/export",
                "health": "/health"
            },
            "database_ready": theater_manager is not None and theater_manager.mysql_manager is not None
        }
    )

@app.get("/health", response_model=ApiResponse)
async def health_check():
    """健康检查"""
    try:
        if not theater_manager or not theater_manager.mysql_manager:
            return ApiResponse(
                success=False,
                message="数据库未初始化",
                error_code="DATABASE_NOT_READY"
            )
        
        # 测试数据库连接
        with theater_manager.mysql_manager.get_connection() as connection:
            cursor = connection.cursor()
            cursor.execute("SELECT COUNT(*) FROM theaters")
            theater_count = cursor.fetchone()[0]
            cursor.close()
        
        return ApiResponse(
            success=True,
            message="服务正常运行",
            data={
                "status": "healthy",
                "theater_count": theater_count,
                "timestamp": datetime.now().isoformat()
            }
        )
    except Exception as e:
        return ApiResponse(
            success=False,
            message=f"健康检查失败: {str(e)}",
            error_code="HEALTH_CHECK_FAILED"
        )

@app.get("/theaters", response_model=ApiResponse)
async def get_theaters(
    limit: Optional[int] = Query(None, description="限制返回数量", ge=1, le=1000),
    offset: int = Query(0, description="偏移量", ge=0),
    search: Optional[str] = Query(None, description="搜索关键词")
):
    """获取剧场列表"""
    try:
        if not theater_manager:
            raise HTTPException(status_code=503, detail="服务未初始化")
        
        result = theater_manager.get_all_theaters(
            limit=limit,
            offset=offset,
            search=search
        )
        
        if result['success']:
            return ApiResponse(
                success=True,
                message="获取剧场列表成功",
                data=result
            )
        else:
            raise HTTPException(
                status_code=500,
                detail=result.get('error', '获取剧场列表失败')
            )
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"获取剧场列表失败: {str(e)}"
        )

@app.get("/theaters/{theater_id}", response_model=ApiResponse)
async def get_theater_detail(
    theater_id: int = Path(..., description="剧场ID", gt=0)
):
    """获取剧场详细信息"""
    try:
        if not theater_manager:
            raise HTTPException(status_code=503, detail="服务未初始化")
        
        result = theater_manager.get_theater_by_id(theater_id)
        
        if result['success']:
            return ApiResponse(
                success=True,
                message="获取剧场详情成功",
                data=result
            )
        else:
            if "不存在" in result.get('error', ''):
                raise HTTPException(status_code=404, detail=result['error'])
            else:
                raise HTTPException(status_code=500, detail=result.get('error', '获取剧场详情失败'))
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"获取剧场详情失败: {str(e)}"
        )

@app.get("/statistics", response_model=ApiResponse)
async def get_statistics():
    """获取剧场统计信息"""
    try:
        if not theater_manager:
            raise HTTPException(status_code=503, detail="服务未初始化")
        
        result = theater_manager.get_theater_statistics()
        
        if result['success']:
            return ApiResponse(
                success=True,
                message="获取统计信息成功",
                data=result
            )
        else:
            raise HTTPException(
                status_code=500,
                detail=result.get('error', '获取统计信息失败')
            )
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"获取统计信息失败: {str(e)}"
        )

@app.post("/export", response_model=ApiResponse)
async def export_theaters(export_request: ExportRequest):
    """导出剧场数据"""
    try:
        if not theater_manager:
            raise HTTPException(status_code=503, detail="服务未初始化")
        
        # 生成文件名
        if not export_request.filename:
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            export_request.filename = f"theaters_export_{timestamp}.{export_request.format}"
        
        result = theater_manager.export_theaters_to_file(
            filename=export_request.filename,
            format_type=export_request.format,
            limit=export_request.limit,
            offset=export_request.offset,
            search=export_request.search
        )
        
        if result['success']:
            return ApiResponse(
                success=True,
                message="数据导出成功",
                data=result
            )
        else:
            raise HTTPException(
                status_code=500,
                detail=result.get('error', '数据导出失败')
            )
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"数据导出失败: {str(e)}"
        )

@app.get("/export/{filename}/download")
async def download_export_file(filename: str):
    """下载导出的文件"""
    try:
        file_path = os.path.join("theater_exports", filename)
        
        if not os.path.exists(file_path):
            raise HTTPException(status_code=404, detail="文件不存在")
        
        return FileResponse(
            path=file_path,
            filename=filename,
            media_type='application/octet-stream'
        )
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"文件下载失败: {str(e)}"
        )

@app.get("/summary", response_model=ApiResponse)
async def get_theater_summary():
    """获取剧场摘要信息"""
    try:
        if not theater_manager:
            raise HTTPException(status_code=503, detail="服务未初始化")
        
        # 获取统计信息
        stats_result = theater_manager.get_theater_statistics()
        
        # 获取最新5个剧场
        recent_result = theater_manager.get_all_theaters(limit=5)
        
        summary_data = {
            "overview": stats_result.get('statistics', {}).get('basic', {}) if stats_result.get('success') else {},
            "recent_theaters": recent_result.get('theaters', []) if recent_result.get('success') else [],
            "total_count": recent_result.get('total', 0) if recent_result.get('success') else 0,
            "chat_activity": stats_result.get('statistics', {}).get('chat_activity', {}) if stats_result.get('success') else {},
            "timestamp": datetime.now().isoformat()
        }
        
        return ApiResponse(
            success=True,
            message="获取剧场摘要成功",
            data=summary_data
        )
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"获取剧场摘要失败: {str(e)}"
        )

@app.get("/search", response_model=ApiResponse)
async def search_theaters(
    q: str = Query(..., description="搜索关键词"),
    limit: int = Query(20, description="限制返回数量", ge=1, le=100)
):
    """搜索剧场"""
    try:
        if not theater_manager:
            raise HTTPException(status_code=503, detail="服务未初始化")
        
        result = theater_manager.get_all_theaters(
            limit=limit,
            search=q
        )
        
        if result['success']:
            return ApiResponse(
                success=True,
                message=f"搜索到 {result['count']} 个剧场",
                data={
                    "search_keyword": q,
                    "results": result
                }
            )
        else:
            raise HTTPException(
                status_code=500,
                detail=result.get('error', '搜索失败')
            )
    
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"搜索失败: {str(e)}"
        )

# ==================== 异常处理 ====================

@app.exception_handler(HTTPException)
async def http_exception_handler(request, exc):
    return JSONResponse(
        status_code=exc.status_code,
        content={
            "success": False,
            "message": str(exc.detail),
            "error_code": "HTTP_ERROR"
        }
    )

@app.exception_handler(Exception)
async def general_exception_handler(request, exc):
    print(f"❌ 服务器错误: {exc}")
    return JSONResponse(
        status_code=500,
        content={
            "success": False,
            "message": f"服务器内部错误: {str(exc)}",
            "error_code": "INTERNAL_SERVER_ERROR"
        }
    )

# ==================== 启动配置 ====================

def main():
    """主函数"""
    print("🎭 启动剧场列表API服务...")
    print(f"📖 API文档: http://localhost:8802/docs")
    print(f"🔍 Redoc文档: http://localhost:8802/redoc")
    print("💡 按 Ctrl+C 停止服务")
    print()
    print("🚀 支持的功能:")
    print("   - 📋 剧场列表查询 (/theaters)")
    print("   - 🔍 剧场详情获取 (/theaters/{id})")
    print("   - 📊 统计信息分析 (/statistics)")
    print("   - 📤 数据导出功能 (/export)")
    print("   - 🔍 剧场搜索功能 (/search)")
    print("   - 📝 剧场摘要信息 (/summary)")
    print("   - 🏥 健康状态检查 (/health)")
    print()
    print("📁 数据存储:")
    print(f"   - MySQL数据库: theam.theaters")
    print(f"   - 导出文件目录: theater_exports/")
    
    return True

if __name__ == "__main__":
    if main():
        uvicorn.run(
            "theater_list:app",  # 🔧 修复：使用模块字符串格式
            host="0.0.0.0",
            port=8802,
            reload=True,
            log_level="info",
            access_log=True
        )
